package com.aizhixin.lab.project.template.service;

import com.aizhixin.lab.common.core.ApiReturnConstants;
import com.aizhixin.lab.common.core.DataValidity;
import com.aizhixin.lab.common.core.PageData;
import com.aizhixin.lab.common.core.PageUtil;
import com.aizhixin.lab.common.domain.PageDomain;
import com.aizhixin.lab.common.dto.SortDTO;
import com.aizhixin.lab.common.utils.ExcelUtil;
import com.aizhixin.lab.common.utils.PageJdbcUtil;
import com.aizhixin.lab.company.repository.UserRepository;
import com.aizhixin.lab.course.template.domain.TemplateDomain;
import com.aizhixin.lab.course.template.entity.Template;
import com.aizhixin.lab.post.entity.Business;
import com.aizhixin.lab.post.entity.Post;
import com.aizhixin.lab.post.entity.PostSkill;
import com.aizhixin.lab.post.entity.SkillPoints;
import com.aizhixin.lab.post.repository.BusinessRepository;
import com.aizhixin.lab.post.server.PostService;
import com.aizhixin.lab.post.server.PostSkillService;
import com.aizhixin.lab.post.server.SkillPointsService;
import com.aizhixin.lab.project.template.data.NewProjectData;
import com.aizhixin.lab.project.template.data.ProjectData;
import com.aizhixin.lab.project.template.domain.*;
import com.aizhixin.lab.project.template.entity.*;
import com.aizhixin.lab.project.template.repository.*;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import sun.security.util.Length;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
@Transactional
public class ProjectService {
    @Autowired
    private ProjectRepository projectRepository;
    @Autowired
    private ProjectVideoService videoService;
    @Autowired
    private ProjectVideoRepository videoRepository;
    @Autowired
    private TaskWorkOrderFileRepository workOrderFileRepository;
    @Autowired
    private ModuleTaskFileRepository taskFileRepository;
    @Autowired
    private PostSkillService skillService;
    @Autowired
    private ProjectFileRepository fileRepository;
    @Autowired
    private PageJdbcUtil pageJdbcUtil;
    @Autowired
    private ProjectPostService projectPostService;
    @Autowired
    private ProjectSkillService projectSkillService;
    @Autowired
    private ProjectSkillPointsService projectSkillPointsService;
    @Autowired
    private ProjectCourseService courseService;
    @Autowired
    private ProjectModuleRepository moduleRepository;
    @Autowired
    private ModuleTaskRepository moduleTaskRepository;
    @Autowired
    private TaskWorkOrderRepository workOrderRepository;
    @Autowired
    private KnowledgePointFileRepository knowledgePointFileRepository;
    @Autowired
    private KnowledgePointRepository knowledgePointRepository;
    @Autowired
    private BusinessRepository businessRepository;
    @Autowired
    private UserRepository userRepository;
    @Autowired
    private LanguageRepository languageRepository;
    public Project save(ProjectDomain domain, Long id) {
        Project project = null;
        Boolean isNew = false;
        List<ProjectPost> projectPostList = new ArrayList<>();
        List<ProjectSkill> projectSkillList = new ArrayList<>();
        List<ProjectVideo> projectVideoList = new ArrayList<>();
        List<ProjectSkillPoints> projectSkillPointsList = new ArrayList<>();
        List<ProjectFile> fileList = new ArrayList<>();
        List<ProjectCourse> courseList = new ArrayList<>();
        if (null == domain.getId() || "".equals(domain.getId())) {
            domain.setId(UUID.randomUUID().toString());
            project = new Project();
            project.setDeleteFlag(DataValidity.VALID.getState());
            project.setCreatedDate(new Date());
            project.setIsPublish(DataValidity.VALID.getState());
            project.setModuleNum(0);
            project.setTaskNum(0);
            project.setWorkOrderNum(0);
            project.setCreatedBy(id);
            isNew = true;
        } else {
            project = projectRepository.findOne(domain.getId());
        }
        domain.setIsReply(project.getIsReply());
        domain.setIsPublish(project.getIsPublish());
        domain.setModuleNum(project.getModuleNum());
        domain.setTaskNum(project.getTaskNum());
        domain.setWorkOrderNum(project.getWorkOrderNum());
        domain.setCreatedDate(project.getCreatedDate());
        BeanUtils.copyProperties(domain, project);
        if (domain.getProjectTeachingPlan() != null) {
            domain.getProjectTeachingPlan().forEach(item -> {
                ProjectFile projectFile = new ProjectFile();
                BeanUtils.copyProperties(item, projectFile);
                projectFile.setId(UUID.randomUUID().toString());
                projectFile.setProjectId(domain.getId());
                fileList.add(projectFile);
            });
        }
        if (domain.getProjectPostDomainList() != null) {
            for (ProjectPostDomain postDomain : domain.getProjectPostDomainList()) {
                ProjectPost post = new ProjectPost();
                BeanUtils.copyProperties(postDomain, post);
                String transferability = "";
                if (postDomain.getTransferabilityList() != null) {
                    for (String s : postDomain.getTransferabilityList()) {
                        transferability += s + ",";
                    }
                    if (transferability.length() > 1) {
                        transferability = transferability.substring(0, transferability.length() - 1);
                    }
                }
                post.setTransferability(transferability);
                post.setProjectId(domain.getId());
                post.setId(UUID.randomUUID().toString());
                post.setPostId(StringUtils.isEmpty(postDomain.getPostId()) == true ? postDomain.getId() : postDomain.getPostId());
                post.setDeleteFlag(DataValidity.VALID.getState());
                if (postDomain.getProjectSkillDomainList() != null) {
                    for (ProjectSkillDomain skillDomain : postDomain.getProjectSkillDomainList()) {
                        ProjectSkill projectSkill = new ProjectSkill();
                        BeanUtils.copyProperties(skillDomain, projectSkill);
                        projectSkill.setPostId(post.getId());
                        projectSkill.setSkillId(projectSkill.getId());
                        projectSkill.setId(UUID.randomUUID().toString());
                        projectSkill.setDeleteFlag(DataValidity.VALID.getState());
                        if (skillDomain.getPointsList() != null) {
                            for (SkillPointsDomain pointsDomain : skillDomain.getPointsList()) {
                                ProjectSkillPoints projectSkillPoints = new ProjectSkillPoints();
                                BeanUtils.copyProperties(pointsDomain, projectSkillPoints);
                                projectSkillPoints.setId(UUID.randomUUID().toString());
                                projectSkillPoints.setSkillId(projectSkill.getId());
                                projectSkillPointsList.add(projectSkillPoints);
                            }
                        }
                        projectSkillList.add(projectSkill);
                    }
                }
                projectPostList.add(post);
            }
        }

        if (domain.getProjectVideoDomainList() != null) {
            for (ProjectVideoDomain videoDomain : domain.getProjectVideoDomainList()) {
                ProjectVideo projectVideo = new ProjectVideo();
                videoDomain.setId(UUID.randomUUID().toString());
                videoDomain.setProjectId(project.getId());
                BeanUtils.copyProperties(videoDomain, projectVideo);
                projectVideo.setDeleteFlag(DataValidity.VALID.getState());
                projectVideoList.add(projectVideo);
            }
        }
        if (domain.getProjectCourseList() != null) {
            for (ProjectCourseDomain courseDomain : domain.getProjectCourseList()) {
                ProjectCourse projectCourse = new ProjectCourse();
                BeanUtils.copyProperties(courseDomain, projectCourse);
                projectCourse.setId(UUID.randomUUID().toString());
                projectCourse.setProjectId(domain.getId());
                courseList.add(projectCourse);
            }
        }
        String images = "";
        if (domain.getProjectScreenshots() != null) {
            for (String s : domain.getProjectScreenshots()) {
                images += s + ",";
            }
            if (images.length() > 1) {
                images = images.substring(0, images.length() - 1);
            }
        }
        String language = domain.getLanguageList();
//        if (domain.getLanguageList() != null) {
//            for (Language s : domain.getLanguageList()) {
//                language += s.getLanguage() + ",";
//            }
//            if (language.length() > 1) {
//                language = language.substring(0, language.length() - 1);
//            }
//        }

        project.setLanguage(language);
        project.setProjectScreenshots(images);
        if (!isNew) {
            deleteProject(domain.getId());
            courseService.deleteByProjectId(domain.getId());
            videoService.deleteByProjectId(domain.getId());
            fileRepository.deleteByProjectId(domain.getId());
        }
        fileRepository.save(fileList);
        courseService.save(courseList);
        videoService.save(projectVideoList);
        projectPostService.save(projectPostList);
        projectSkillService.save(projectSkillList);
        projectSkillPointsService.save(projectSkillPointsList);
        return projectRepository.save(project);
    }

    public Project findProject(String id) {
        return projectRepository.findOne(id);
    }
    RowMapper<ProjectDomain> projectDomainRowMapper = new RowMapper<ProjectDomain>() {

        @Override
        public ProjectDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            ProjectDomain projectDomain = new ProjectDomain();
            projectDomain.setId(rs.getString("id"));
            projectDomain.setProjectName(rs.getString("project_name"));
            projectDomain.setProjectImageUrl(rs.getString("project_image_url"));
            projectDomain.setIsPublish(rs.getInt("is_publish"));
            projectDomain.setModuleNum(rs.getInt("module_num"));
            projectDomain.setTaskNum(rs.getInt("task_num"));
            projectDomain.setWorkOrderNum(rs.getInt("work_order_num"));
            projectDomain.setClassHour(rs.getFloat("class_hour"));
            projectDomain.setProjectType(rs.getString("project_type"));
            try {
                projectDomain.setCreateBy(userRepository.findOne(rs.getLong("created_by")).getName());
            }catch (NullPointerException e){
            }
            return projectDomain;
        }
    };
    public Map<String,Object> findProject(QueryProjectDomain domain, Long id) {
        String sql="SELECT * FROM t_project WHERE delete_flag=0";
        String countSql="SELECT count(*) FROM t_project WHERE delete_flag=0";
        if (!StringUtils.isEmpty(domain.getProjectName())){
            sql+=" AND project_name LIKE '%"+domain.getProjectName()+"%'";
            countSql+=" AND project_name LIKE '%"+domain.getProjectName()+"%'";
        }
        if (!StringUtils.isEmpty(domain.getProjectType())){
            sql+=" AND project_type='"+domain.getProjectType()+"'";
            countSql+=" AND project_type='"+domain.getProjectType()+"'";
        }
        if (domain.getIsPublish()!=null){
            sql+=" AND is_publish="+domain.getIsPublish();
            countSql+=" AND is_publish="+domain.getIsPublish();
        }
        if (domain.getProjectGenreId()!=null){
            sql+=" AND project_genre_id="+domain.getProjectGenreId();
            countSql+=" AND project_genre_id="+domain.getProjectGenreId();
        }
        if (id!=null){
            sql+=" AND created_by="+id;
            countSql+=" AND created_by="+id;
        }
        List<SortDTO> sortDTOList=new ArrayList<>();
        SortDTO sortDTO = new SortDTO();
        sortDTO.setKey("created_date");
        sortDTO.setAsc(false);
        sortDTOList.add(sortDTO);
       return pageJdbcUtil.getPageInfor(domain.getPageSize(),domain.getPageNumber(),projectDomainRowMapper,sortDTOList,sql,countSql);
//        Pageable pageable = PageUtil.createRequestAndSortType(domain.getPageNumber(), domain.getPageSize(), "desc", "createdDate");
//        Page<Project> projectPage = null;
//        if (StringUtils.isEmpty(domain.getProjectName())) {
//            if (domain.getIsPublish() != null) {
//
//                if (!StringUtils.isEmpty(domain.getProjectType())) {
//                    projectPage = projectRepository.findByIsPublishAndProjectTypeAndDeleteFlag(pageable, domain.getIsPublish(), domain.getProjectType(), DataValidity.VALID.getState());
//                } else {
//                    projectPage = projectRepository.findByIsPublishAndDeleteFlag(pageable, domain.getIsPublish(), DataValidity.VALID.getState());
//                }
//            } else {
//                if (!StringUtils.isEmpty(domain.getProjectType())) {
//                    projectPage = projectRepository.findByProjectTypeAndDeleteFlag(pageable, domain.getProjectType(), DataValidity.VALID.getState());
//                } else {
//                    projectPage = projectRepository.findByDeleteFlag(pageable, DataValidity.VALID.getState());
//                }
//            }
//        } else {
//            String name = "%" + domain.getProjectName() + "%";
//            if (domain.getIsPublish() != null) {
//                if (!StringUtils.isEmpty(domain.getProjectType())) {
//                    projectPage = projectRepository.findByIsPublishAndProjectTypeAndProjectNameIsLikeAndDeleteFlag(pageable, domain.getIsPublish(), domain.getProjectType(), name, DataValidity.VALID.getState());
//                } else {
//                    projectPage = projectRepository.findByIsPublishAndProjectNameIsLikeAndDeleteFlag(pageable, domain.getIsPublish(), name, DataValidity.VALID.getState());
//                }
//
//            } else {
//                if (!StringUtils.isEmpty(domain.getProjectType())) {
//                    projectPage = projectRepository.findByProjectTypeAndProjectNameIsLikeAndDeleteFlag(pageable, domain.getProjectType(), name, DataValidity.VALID.getState());
//                } else {
//                    projectPage = projectRepository.findByProjectNameIsLikeAndDeleteFlag(pageable, name, DataValidity.VALID.getState());
//                }
//            }
//        }
//        PageDomain pageDomain = new PageDomain();
//        pageDomain.setPageSize(projectPage.getSize());
//        pageDomain.setPageNumber(projectPage.getNumber());
//        pageDomain.setTotalElements(projectPage.getTotalElements());
//        pageDomain.setTotalPages(projectPage.getTotalPages());
//        List<ProjectDomain> projectDomains = new ArrayList<>();
//        for (Project p : projectPage.getContent()) {
//            ProjectDomain projectDomain = new ProjectDomain();
//            BeanUtils.copyProperties(p, projectDomain);
//            projectDomains.add(projectDomain);
//        }
//        PageData<ProjectDomain> pageData = new PageData<>();
//        pageData.setData(projectDomains);
//        pageData.setPage(pageDomain);
//        return pageData;
    }

//    public PageData<ProjectDomain> findProject(QueryProjectDomain domain, Long id) {
//        Pageable pageable = PageUtil.createRequestAndSortType(domain.getPageNumber(), domain.getPageSize(), "desc", "createdDate");
//        Page<Project> projectPage = null;
//        if (StringUtils.isEmpty(domain.getProjectName())) {
//            if (domain.getIsPublish() != null) {
//                if (!StringUtils.isEmpty(domain.getProjectType())) {
//                    projectPage = projectRepository.findByIsPublishAndProjectTypeAndDeleteFlagAndCreatedBy(pageable, domain.getIsPublish(), domain.getProjectType(), DataValidity.VALID.getState(), id);
//                } else {
//                    projectPage = projectRepository.findByIsPublishAndDeleteFlagAndCreatedBy(pageable, domain.getIsPublish(), DataValidity.VALID.getState(), id);
//                }
//            } else {
//                if (!StringUtils.isEmpty(domain.getProjectType())) {
//                    projectPage = projectRepository.findByProjectTypeAndDeleteFlagAndCreatedBy(pageable, domain.getProjectType(), DataValidity.VALID.getState(), id);
//                } else {
//                    projectPage = projectRepository.findByDeleteFlagAndCreatedBy(pageable, DataValidity.VALID.getState(), id);
//                }
//            }
//        } else {
//            String name = "%" + domain.getProjectName() + "%";
//            if (domain.getIsPublish() != null) {
//                if (!StringUtils.isEmpty(domain.getProjectType())) {
//                    projectPage = projectRepository.findByIsPublishAndProjectTypeAndProjectNameIsLikeAndDeleteFlagAndCreatedBy(pageable, domain.getIsPublish(), domain.getProjectType(), name, DataValidity.VALID.getState(), id);
//                } else {
//                    projectPage = projectRepository.findByIsPublishAndProjectNameIsLikeAndDeleteFlagAndCreatedBy(pageable, domain.getIsPublish(), name, DataValidity.VALID.getState(), id);
//                }
//
//            } else {
//                if (!StringUtils.isEmpty(domain.getProjectType())) {
//                    projectPage = projectRepository.findByProjectTypeAndProjectNameIsLikeAndDeleteFlagAndCreatedBy(pageable, domain.getProjectType(), name, DataValidity.VALID.getState(), id);
//                } else {
//                    projectPage = projectRepository.findByProjectNameIsLikeAndDeleteFlagAndCreatedBy(pageable, name, DataValidity.VALID.getState(), id);
//                }
//            }
//        }
//        PageDomain pageDomain = new PageDomain();
//        pageDomain.setPageSize(projectPage.getSize());
//        pageDomain.setPageNumber(projectPage.getNumber());
//        pageDomain.setTotalElements(projectPage.getTotalElements());
//        pageDomain.setTotalPages(projectPage.getTotalPages());
//        List<ProjectDomain> projectDomains = new ArrayList<>();
//        for (Project p : projectPage.getContent()) {
//            ProjectDomain projectDomain = new ProjectDomain();
//            projectDomain.setCreateBy(userRepository.findOne(p.getCreatedBy()).getName());
//            BeanUtils.copyProperties(p, projectDomain);
//            projectDomains.add(projectDomain);
//        }
//        PageData<ProjectDomain> pageData = new PageData<>();
//        pageData.setData(projectDomains);
//        pageData.setPage(pageDomain);
//        return pageData;
//    }

    public Project save(Project project) {
        return projectRepository.save(project);
    }

    public NewProjectData findProjectDomain(String id) {
        Project project = projectRepository.findOne(id);
        NewProjectData projectDomain = new NewProjectData();
        BeanUtils.copyProperties(project, projectDomain);
        List<String> projectScreenshots = new ArrayList<>();
        List<String> languageList = new ArrayList<>();
//        List skillList = projectSkillService.findSkillList(project.getId());

        List<ProjectPostDomain> projectPostDomainList = new ArrayList<>();
        List<ProjectPost> projectPostList = projectPostService.findProjectPostList(id);
        for (ProjectPost post : projectPostList) {
            ProjectPostDomain projectPostDomain = new ProjectPostDomain();
            Business business = businessRepository.findByIdAndDeleteFlag(post.getBusinessId(), DataValidity.VALID.getState());
            if (!Objects.isNull(business)){
                projectPostDomain.setBusinessName(business.getBusinessName());
            }
            BeanUtils.copyProperties(post, projectPostDomain);
            String pointNumSql = "SELECT COUNT(*) FROM t_skill_points tsp LEFT JOIN t_project_skill tps ON tsp.skill_id=tsp.id WHERE tps.post_id='" + post.getId() + "'";
            Integer pointNum = pageJdbcUtil.getCountInt(pointNumSql);
            Integer skillNum = projectSkillService.skillNum(post.getId());
            String numSql = "SELECT count(*) FROM p_skill_points psp LEFT JOIN p_post_skill pps ON psp.skill_id=pps.id WHERE pps.post_id='" + post.getPostId() + "'";
            Integer countInt = pageJdbcUtil.getCountInt(numSql);
            Integer skillNum1 = skillService.skillNum(post.getPostId());
            if (skillNum1 + countInt == 0) {
                projectPostDomain.setProportion(0);
            } else {
                projectPostDomain.setProportion((pointNum + skillNum) * 100 / (skillNum1 + countInt));
            }
            if (!StringUtils.isEmpty(post.getTransferability())) {
                projectPostDomain.setTransferabilityList(Arrays.asList(post.getTransferability().split(",")));
            }
            List<ProjectSkill> skillList = projectSkillService.findSkillList(post.getId());
            List<ProjectSkillDomain> skillDomainList = new ArrayList<>();
            for (ProjectSkill projectSkill : skillList) {
                ProjectSkillDomain projectSkillDomain = new ProjectSkillDomain();
                BeanUtils.copyProperties(projectSkill, projectSkillDomain);
                List<ProjectSkillPoints> skillPointsList = projectSkillPointsService.findSkillPointsList(projectSkill.getId());

                List<SkillPointsDomain> pointsDomainList = new ArrayList<>();
                for (ProjectSkillPoints skillPoints : skillPointsList) {
                    SkillPointsDomain pointsDomain = new SkillPointsDomain();
                    BeanUtils.copyProperties(skillPoints, pointsDomain);
                    pointsDomainList.add(pointsDomain);
                }
                projectSkillDomain.setPointsList(pointsDomainList);
                skillDomainList.add(projectSkillDomain);

            }
            projectPostDomain.setProjectSkillDomainList(skillDomainList);
            projectPostDomainList.add(projectPostDomain);
        }


        List<ProjectVideoDomain> projectVideoDomainList = new ArrayList<>();
        List<ProjectVideo> videoList = videoService.findVideoList(id);
        for (ProjectVideo projectVideo : videoList) {
            ProjectVideoDomain projectVideoDomain = new ProjectVideoDomain();
            BeanUtils.copyProperties(projectVideo, projectVideoDomain);
            projectVideoDomainList.add(projectVideoDomain);
        }
        List<ProjectCourseDomain> courseDomainList = new ArrayList<>();
        List<ProjectCourse> courseList = courseService.projectCourseListByProjectId(id);
        for (ProjectCourse projectCourse : courseList) {
            ProjectCourseDomain courseDomain = new ProjectCourseDomain();
            BeanUtils.copyProperties(projectCourse, courseDomain);
            courseDomainList.add(courseDomain);
        }
        if (project.getLanguage() != null) {
            List<String> stringList = Arrays.asList(project.getLanguage().split(","));
            projectDomain.setLanguageList(languageRepository.findByIdInAndDeleteFlag(stringList,DataValidity.VALID.getState()));
        }
        if (project.getProjectScreenshots() != null) {
            projectDomain.setProjectScreenshots(Arrays.asList(project.getProjectScreenshots().split(",")));
        }
        List<ProjectFileDomain> fileDomainList = new ArrayList<>();
        List<ProjectFile> projectFiles = fileRepository.findByProjectIdOrderByCreatedDateAsc(id);
        projectFiles.forEach(item -> {
            ProjectFileDomain projectFileDomain = new ProjectFileDomain();
            BeanUtils.copyProperties(item, projectFileDomain);
            fileDomainList.add(projectFileDomain);
        });
        projectDomain.setProjectTeachingPlan(fileDomainList);
        projectDomain.setProjectTotalFileNum(countProjectFileNum(id));
        projectDomain.setProjectCourseList(courseDomainList);
        projectDomain.setProjectVideoDomainList(projectVideoDomainList);
        projectDomain.setProjectPostDomainList(projectPostDomainList);
        return projectDomain;
    }

    public Map<String, Object> publish(String id) {
        Map<String, Object> result = new HashMap<String, Object>();
        Project project = findProject(id);
        if (!Objects.isNull(project)) {
            project.setIsPublish(project.getIsPublish().equals(DataValidity.VALID.getState()) ? DataValidity.INVALID.getState() : DataValidity.VALID.getState());
            save(project);
            result.put(ApiReturnConstants.SUCCESS, true);
            result.put(ApiReturnConstants.DATA, "发布成功");
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "未找到项目信息");
        }
        return result;

    }

    public Map<String, Object> delete(String id) {
        Map<String, Object> result = new HashMap<String, Object>();
        Project project = findProject(id);
        if (!Objects.isNull(project)) {
            project.setDeleteFlag(DataValidity.INVALID.getState());
            save(project);
            List<ProjectModule> projectModuleList = moduleRepository.findByProjectIdAndDeleteFlagOrderByModuleNo(id, DataValidity.VALID.getState());
            projectModuleList.forEach(item->{
                item.setDeleteFlag(DataValidity.INVALID.getState());
            });
            List<String> collect = projectModuleList.stream().map(ProjectModule::getId).collect(Collectors.toList());
            List<ModuleTask> moduleTaskList = moduleTaskRepository.findByModuleIdInAndDeleteFlag(collect, DataValidity.VALID.getState());
            moduleTaskList.forEach(item->{
                item.setDeleteFlag(DataValidity.INVALID.getState());
            });
            List<String> collect1 = moduleTaskList.stream().map(ModuleTask::getId).collect(Collectors.toList());
            List<TaskWorkOrder> taskWorkOrderList = workOrderRepository.findByTaskIdInAndDeleteFlag(collect1, DataValidity.VALID.getState());
            taskWorkOrderList.forEach(item->{
                item.setDeleteFlag(DataValidity.INVALID.getState());
            });
            moduleRepository.save(projectModuleList);
            moduleTaskRepository.save(moduleTaskList);
            workOrderRepository.save(taskWorkOrderList);
            result.put(ApiReturnConstants.SUCCESS, true);
            result.put(ApiReturnConstants.DATA, "删除成功");
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "未找到项目信息");
        }
        return result;
    }

    public Map<String, Object> updateProject(ProjectDomain domain) {
        Map<String, Object> result = new HashMap<String, Object>();
        Project project = projectRepository.findOne(domain.getId());
        if (Objects.isNull(project)) {
            BeanUtils.copyProperties(domain, project);
            projectRepository.save(project);
            result.put(ApiReturnConstants.SUCCESS, true);
            result.put(ApiReturnConstants.DATA, "修改成功");
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "项目信息不存在");
        }
        return result;
    }

    public Integer deleteProject(String id) {
        String sql = "DELETE tpp,tps,tsp  FROM t_project tp LEFT JOIN t_project_post tpp ON tp.id=tpp.project_id LEFT JOIN t_project_skill tps ON tpp.id=tps.post_id LEFT JOIN t_skill_points tsp ON tps.id=tsp.skill_id  WHERE tp.id='" + id + "'";
        return pageJdbcUtil.delete(sql);
    }

    RowMapper<String> idRm = new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            return rs.getString(1);
        }
    };

    //获取该项目下的所有文件
    private Integer countProjectFileNum(String id) {
//        String sql = "SELECT COUNT(DISTINCT a.file_id) num FROM (SELECT tpv.id file_id, tpv.project_id project_id FROM c_project_video tpv LEFT JOIN c_project tp ON tpv.project_id=tp.id UNION ALL SELECT tmtf.id file_id,tpm.project_id project_id FROM c_module_task_file tmtf LEFT JOIN c_module_task tmt ON tmtf.task_id=tmt.id LEFT JOIN c_project_module tpm ON tmt.module_id=tpm.id UNION ALL SELECT ttwof.id file_id,tpm.project_id project_id FROM c_task_work_order_file ttwof LEFT JOIN c_task_work_order ttwo ON  ttwof.work_order_id=ttwo.id LEFT JOIN c_module_task tmt ON ttwo.task_id=tmt.id LEFT JOIN c_project_module tpm ON tmt.module_id=tpm.id) a WHERE a.project_id='" + id + "'";
        Integer integer = videoRepository.countByProjectIdAndDeleteFlag(id, DataValidity.VALID.getState());
        Integer taskFileNum = 0;
        Integer workOrderFileNum = 0;
        List<String> info = pageJdbcUtil.getInfo("SELECT id FROM c_project_module WHERE project_id='" + id + "' AND delete_flag=0", idRm);
        if (info.size() > 0) {
            List<String> taskId = pageJdbcUtil.getInfo("SELECT id FROM c_module_task WHERE module_id in (" + listToString(info) + ") AND delete_flag=0", idRm);
            if (taskId.size() > 0) {
                taskFileNum = taskFileRepository.countByTaskIdInAndDeleteFlag(taskId, DataValidity.VALID.getState());
                List<String> workOrderId = pageJdbcUtil.getInfo("SELECT id FROM c_task_work_order WHERE task_id in (" + listToString(taskId) + ") AND delete_flag=0", idRm);

                if (workOrderId.size() > 0) {
                    workOrderFileNum = workOrderFileRepository.countByWorkOrderIdInAndDeleteFlag(workOrderId, DataValidity.VALID.getState());
                }
            }
        }
        return integer + taskFileNum + workOrderFileNum;
    }

    public String listToString(List<String> list) {
        String s = "";
        for (int i = 0; i < list.size(); i++) {
            s += "'" + list.get(i) + "'";
            if (i != list.size() - 1) {
                s += ",";
            }
        }
        return s;
    }

    public HSSFWorkbook exportProject(String projectId) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet1 = wb.createSheet("project");
        HSSFSheet sheet2 = wb.createSheet("projectFileList");
        HSSFSheet sheet3 = wb.createSheet("moduleList");
        HSSFSheet sheet4 = wb.createSheet("taskList");
        HSSFSheet sheet5 = wb.createSheet("workOrderList");
        HSSFSheet sheet6 = wb.createSheet("taskFileList");
        HSSFSheet sheet7 = wb.createSheet("workOrderFileList");
        HSSFSheet sheet8 = wb.createSheet("knowledgePointList");
        HSSFSheet sheet9 = wb.createSheet("knowledgePointFileList");
        HSSFSheet sheet10 = wb.createSheet("vedio");
        Project project = projectRepository.findOne(projectId);
        HSSFRow row = sheet1.createRow(1);

        row.createCell(1).setCellValue(project.getId());
        row.createCell(2).setCellValue(project.getProjectName());
        row.createCell(3).setCellValue(project.getProjectDesc());
        row.createCell(4).setCellValue(project.getProjectImageUrl());
        row.createCell(5).setCellValue(project.getLanguage());
        row.createCell(6).setCellValue(project.getTaskNum());
        row.createCell(7).setCellValue(project.getWorkOrderNum());
        row.createCell(8).setCellValue(project.getModuleNum());
        row.createCell(9).setCellValue(project.getClassHour());
        row.createCell(10).setCellValue(project.getProjectType());
        row.createCell(11).setCellValue(project.getProjectScreenshots());
        try {
            row.createCell(12).setCellValue(project.getIsReply());
        } catch (NullPointerException e) {

        }
        row.createCell(13).setCellValue(project.getFileFirstImg());
        try {
            row.createCell(14).setCellValue(project.getVmName());
            row.createCell(15).setCellValue(project.getVmNum());
            row.createCell(16).setCellValue(project.getVersion());
        } catch (NullPointerException e) {

        }

        int i = 1;
        List<ProjectFile> projectFileList = fileRepository.findByProjectIdOrderByCreatedDateAsc(projectId);
        for (ProjectFile projectFile : projectFileList) {
            HSSFRow row1 = sheet2.createRow(i);
            row1.createCell(1).setCellValue(projectFile.getId());
            row1.createCell(2).setCellValue(projectFile.getProjectId());
            row1.createCell(3).setCellValue(projectFile.getFileName());
            try {
                row1.createCell(4).setCellValue(projectFile.getFileType());

            } catch (NullPointerException e) {

            }
            row1.createCell(5).setCellValue(projectFile.getFileUrl());
            i++;
        }
        List<ProjectVideo> videoList = videoRepository.findByProjectIdAndDeleteFlag(projectId, DataValidity.VALID.getState());
        i = 1;
        for (ProjectVideo projectVideo : videoList) {
            HSSFRow row1 = sheet10.createRow(i);
            row1.createCell(1).setCellValue(projectVideo.getId());
            row1.createCell(2).setCellValue(projectVideo.getProjectId());
            row1.createCell(3).setCellValue(projectVideo.getVideoName());
            row1.createCell(4).setCellValue(projectVideo.getVideoUrl());
            row1.createCell(5).setCellValue(projectVideo.getFileFirstImg());
        }
        List<ProjectModule> projectModuleList = moduleRepository.findByProjectIdAndDeleteFlagOrderByModuleNo(projectId, DataValidity.VALID.getState());
        i = 1;
        for (ProjectModule projectModule : projectModuleList) {
            HSSFRow row1 = sheet3.createRow(i);
            row1.createCell(1).setCellValue(projectModule.getId());
            row1.createCell(2).setCellValue(projectModule.getProjectId());
            row1.createCell(3).setCellValue(projectModule.getModuleName());
            try {
                row1.createCell(4).setCellValue(projectModule.getModuleDesc());
            }catch (NullPointerException e){

            }
            try {
                row1.createCell(5).setCellValue(projectModule.getModuleLevel());
            }catch (NullPointerException e){

            }
            try {
                row1.createCell(6).setCellValue(projectModule.getSuggestTime());
            }catch (NullPointerException e){

            }
            row1.createCell(7).setCellValue(projectModule.getTaskNum());
            row1.createCell(8).setCellValue(projectModule.getModuleNo());
            i++;
        }
        i = 1;
        List<String> collect = projectModuleList.stream().map(ProjectModule::getId).collect(Collectors.toList());
        List<ModuleTask> moduleTaskList = moduleTaskRepository.findByModuleIdInAndDeleteFlag(collect, DataValidity.VALID.getState());
        List<String> taskIds = moduleTaskList.stream().map(ModuleTask::getId).collect(Collectors.toList());
        for (ModuleTask moduleTask : moduleTaskList) {
            HSSFRow row1 = sheet4.createRow(i);
            row1.createCell(1).setCellValue(moduleTask.getId());
            row1.createCell(2).setCellValue(moduleTask.getModuleId());
            row1.createCell(3).setCellValue(moduleTask.getTaskName());
            row1.createCell(4).setCellValue(moduleTask.getTaskDesc());
            row1.createCell(5).setCellValue(moduleTask.getTaskNo());
            row1.createCell(6).setCellValue(moduleTask.getWorkOrderNum());
            try {
                row1.createCell(7).setCellValue(moduleTask.getSuggestTime());
            }catch (NullPointerException e){
            }
            row1.createCell(8).setCellValue(moduleTask.getTaskLevel());
            row1.createCell(9).setCellValue(moduleTask.getTaskStep());
            i++;
        }
        List<TaskWorkOrder> taskWorkOrderList = workOrderRepository.findByTaskIdInAndDeleteFlag(taskIds, DataValidity.VALID.getState());
        List<String> workOrderIds = taskWorkOrderList.stream().map(TaskWorkOrder::getId).collect(Collectors.toList());
        i = 1;
        for (TaskWorkOrder taskWorkOrder : taskWorkOrderList) {
            HSSFRow row1 = sheet5.createRow(i);
            row1.createCell(1).setCellValue(taskWorkOrder.getId());
            row1.createCell(2).setCellValue(taskWorkOrder.getTaskId());
            row1.createCell(3).setCellValue(taskWorkOrder.getWorkOrderName());
            row1.createCell(4).setCellValue(taskWorkOrder.getWorkOrderDesc());
            row1.createCell(5).setCellValue(taskWorkOrder.getWorkOrderVideo());
            try {
                row1.createCell(6).setCellValue(taskWorkOrder.getSuggestTime());
            }catch (NullPointerException e){

            }
            row1.createCell(7).setCellValue(taskWorkOrder.getWorkOrderNo());
            row1.createCell(8).setCellValue(taskWorkOrder.getWorkOrderLevel());
            row1.createCell(9).setCellValue(taskWorkOrder.getWorkStepUrl());
            row1.createCell(10).setCellValue(taskWorkOrder.getWorkStepName());
            i++;
        }
        i = 1;
        List<ModuleTaskFile> taskFileList = taskFileRepository.findByTaskIdInAndDeleteFlag(taskIds, DataValidity.VALID.getState());
        for (ModuleTaskFile moduleTaskFile : taskFileList) {
            HSSFRow row1 = sheet6.createRow(i);
            row1.createCell(1).setCellValue(moduleTaskFile.getId());
            row1.createCell(2).setCellValue(moduleTaskFile.getTaskId());
            row1.createCell(3).setCellValue(moduleTaskFile.getFileName());
            row1.createCell(4).setCellValue(moduleTaskFile.getFileType());
            row1.createCell(5).setCellValue(moduleTaskFile.getFileUrl());
            row1.createCell(6).setCellValue(moduleTaskFile.getFileFirstImg());
            i++;
        }
        i = 1;
        List<WorkOrderFile> workOrderFileList = workOrderFileRepository.findByWorkOrderIdInAndDeleteFlag(workOrderIds, DataValidity.VALID.getState());
        for (WorkOrderFile workOrderFile : workOrderFileList) {
            HSSFRow row1 = sheet7.createRow(i);
            row1.createCell(1).setCellValue(workOrderFile.getId());
            row1.createCell(2).setCellValue(workOrderFile.getWorkOrderId());
            row1.createCell(3).setCellValue(workOrderFile.getFileName());
            row1.createCell(4).setCellValue(workOrderFile.getFileType());
            row1.createCell(5).setCellValue(workOrderFile.getFileUrl());
            i++;
        }
        i = 1;
        List<KnowledgePoint> knowledgePointList = knowledgePointRepository.findByModuleTaskIdInAndType(taskIds, 20);
        List<String> knowledgePointIds = knowledgePointList.stream().map(KnowledgePoint::getId).collect(Collectors.toList());
        for (KnowledgePoint knowledgePoint : knowledgePointList) {
            HSSFRow row1 = sheet8.createRow(i);
            row1.createCell(1).setCellValue(knowledgePoint.getId());
            row1.createCell(2).setCellValue(knowledgePoint.getModuleTaskId());
            row1.createCell(3).setCellValue(knowledgePoint.getKnowledgePoint());
            row1.createCell(4).setCellValue(knowledgePoint.getLink());
            i++;
        }
        i = 1;
        List<KnowledgePointFile> knowledgePointFileList = knowledgePointFileRepository.findByKnowledgePointIdInAndDeleteFlag(knowledgePointIds, DataValidity.VALID.getState());
        for (KnowledgePointFile knowledgePointFile : knowledgePointFileList) {
            HSSFRow row1 = sheet9.createRow(i);
            row1.createCell(1).setCellValue(knowledgePointFile.getId());
            row1.createCell(2).setCellValue(knowledgePointFile.getKnowledgePointId());
            row1.createCell(3).setCellValue(knowledgePointFile.getFileName());
            row1.createCell(4).setCellValue(knowledgePointFile.getFileType());
            row1.createCell(5).setCellValue(knowledgePointFile.getFileUrl());
            row1.createCell(6).setCellValue(knowledgePointFile.getFileFirstImg());
            i++;
        }
        return wb;
    }

    public void importProject(MultipartFile file, Long createBy) throws IOException {
//        if (!ExcelUtil.isExcel(file.getName())) {
//            return;
//        }
        ExcelUtil excelUtil = new ExcelUtil(file.getInputStream(), "2003");
//        if (ExcelUtil.isExcel2003(file.getName())) {
//            excelUtil = new ExcelUtil(file.getInputStream(), "2003");
//        } else {
//            excelUtil = new ExcelUtil(file.getInputStream(), "2007");
//
//        }
        Sheet sheet1 = excelUtil.getWorkbook().getSheetAt(0);
        Sheet sheet2 = excelUtil.getWorkbook().getSheetAt(1);
        Sheet sheet3 = excelUtil.getWorkbook().getSheetAt(2);
        Sheet sheet4 = excelUtil.getWorkbook().getSheetAt(3);
        Sheet sheet5 = excelUtil.getWorkbook().getSheetAt(4);
        Sheet sheet6 = excelUtil.getWorkbook().getSheetAt(5);
        Sheet sheet7 = excelUtil.getWorkbook().getSheetAt(6);
        Sheet sheet8 = excelUtil.getWorkbook().getSheetAt(7);
        Sheet sheet9 = excelUtil.getWorkbook().getSheetAt(8);
        Sheet sheet10 = excelUtil.getWorkbook().getSheetAt(9);
        Map<String, String> moduleMap = new HashMap<>();
        Map<String, String> taskMap = new HashMap<>();
        Map<String, String> orderMap = new HashMap<>();
        Map<String, String> knowledgePointMap = new HashMap<>();
        Project project = new Project();
        Row row = sheet1.getRow(1);
        project.setId(UUID.randomUUID().toString());
        project.setProjectName(excelUtil.getCellValueToString(row.getCell(2)));
        project.setProjectDesc(excelUtil.getCellValueToString(row.getCell(3)));
        project.setProjectImageUrl(excelUtil.getCellValueToString(row.getCell(4)));
        project.setLanguage(excelUtil.getCellValueToString(row.getCell(5)));
        project.setTaskNum(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(6))));
        project.setWorkOrderNum(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(7))));
        project.setModuleNum(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(8))));
        project.setClassHour(Float.valueOf(excelUtil.getCellValueToString(row.getCell(9))));
        project.setProjectType(excelUtil.getCellValueToString(row.getCell(10)));
        project.setProjectScreenshots(excelUtil.getCellValueToString(row.getCell(11)));
        try {
            project.setIsReply(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(12))));
        } catch (NumberFormatException e) {
            project.setIsReply(0);
        }

        project.setFileFirstImg(excelUtil.getCellValueToString(row.getCell(13)));
        project.setVmName(excelUtil.getCellValueToString(row.getCell(14)));
        try {
            project.setVmNum(Integer.parseInt(excelUtil.getCellValueToString(row.getCell(15))));
        } catch (NumberFormatException e) {
        }
        project.setVersion(excelUtil.getCellValueToString(row.getCell(16)));
        project.setIsPublish(0);
        project.setCreatedBy(createBy);
        project.setDeleteFlag(0);
        project.setCreatedDate(new Date());
        List<ProjectFile> projectFileList = new ArrayList<>();
        List<ProjectModule> projectModuleList = new ArrayList<>();
        List<ModuleTask> moduleTaskList = new ArrayList<>();
        List<TaskWorkOrder> taskWorkOrderList = new ArrayList<>();
        List<ModuleTaskFile> taskFileList = new ArrayList<>();
        List<WorkOrderFile> workOrderFileList = new ArrayList<>();
        List<KnowledgePoint> knowledgePointList = new ArrayList<>();
        List<KnowledgePointFile> knowledgePointFileList = new ArrayList<>();

        int lastRowNum = sheet2.getLastRowNum();
        for (int i = 1; i <= lastRowNum; i++) {
            Row row1 = sheet2.getRow(i);
            ProjectFile projectFile = new ProjectFile();
            projectFile.setId(UUID.randomUUID().toString());
            projectFile.setProjectId(project.getId());
            projectFile.setFileName(excelUtil.getCellValueToString(row1.getCell(3)));
            try {
                projectFile.setFileType(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(4))));

            }catch (NumberFormatException e){

            }
            projectFile.setFileUrl(excelUtil.getCellValueToString(row1.getCell(5)));
            projectFile.setDeleteFlag(0);
            projectFileList.add(projectFile);
        }
        int lastRowNum1 = sheet3.getLastRowNum();
        for (int i = 1; i <= lastRowNum1; i++) {
            Row row1 = sheet3.getRow(i);
            ProjectModule projectModule = new ProjectModule();
            projectModule.setId(UUID.randomUUID().toString());
            projectModule.setProjectId(project.getId());
            projectModule.setModuleName(excelUtil.getCellValueToString(row1.getCell(3)));
            projectModule.setModuleDesc(excelUtil.getCellValueToString(row1.getCell(4)));
            try {
                projectModule.setModuleLevel(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(5))));
            }catch (NumberFormatException e){

            }
            projectModule.setSuggestTime(Float.valueOf(excelUtil.getCellValueToString(row1.getCell(6))));
            projectModule.setTaskNum(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(7))));
            projectModule.setModuleNo(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(8))));
            moduleMap.put(excelUtil.getCellValueToString(row1.getCell(1)), projectModule.getId());
            projectModule.setDeleteFlag(0);
            projectModule.setCreatedDate(new Date());
            projectModuleList.add(projectModule);
        }
        int lastRowNum2 = sheet4.getLastRowNum();
        for (int i = 1; i <= lastRowNum2; i++) {
            Row row1 = sheet4.getRow(i);
            ModuleTask moduleTask = new ModuleTask();
            moduleTask.setId(UUID.randomUUID().toString());
            moduleTask.setModuleId(moduleMap.get(excelUtil.getCellValueToString(row1.getCell(2))));
            moduleTask.setTaskName(excelUtil.getCellValueToString(row1.getCell(3)));
            moduleTask.setTaskDesc(excelUtil.getCellValueToString(row1.getCell(4)));
            moduleTask.setTaskNo(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(5))));
            moduleTask.setWorkOrderNum(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(6))));
            moduleTask.setSuggestTime(Float.valueOf(excelUtil.getCellValueToString(row1.getCell(7))));
            moduleTask.setTaskLevel(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(8))));
            moduleTask.setTaskStep(excelUtil.getCellValueToString(row1.getCell(9)));
            moduleTask.setDeleteFlag(0);
            moduleTask.setCreatedDate(new Date());
            taskMap.put(excelUtil.getCellValueToString(row1.getCell(1)), moduleTask.getId());
            moduleTaskList.add(moduleTask);
        }
        int lastRowNum3 = sheet5.getLastRowNum();
        for (int i = 1; i <= lastRowNum3; i++) {
            Row row1 = sheet5.getRow(i);
            TaskWorkOrder taskWorkOrder = new TaskWorkOrder();
            taskWorkOrder.setId(UUID.randomUUID().toString());
            taskWorkOrder.setTaskId(taskMap.get(excelUtil.getCellValueToString(row1.getCell(2))));
            taskWorkOrder.setWorkOrderName(excelUtil.getCellValueToString(row1.getCell(3)));
            taskWorkOrder.setWorkOrderDesc(excelUtil.getCellValueToString(row1.getCell(4)));
            taskWorkOrder.setWorkOrderVideo(excelUtil.getCellValueToString(row1.getCell(5)));
            taskWorkOrder.setWorkStepUrl(excelUtil.getCellValueToString(row1.getCell(9)));
            taskWorkOrder.setWorkStepName(excelUtil.getCellValueToString(row1.getCell(10)));
            taskWorkOrder.setSuggestTime(Float.valueOf(excelUtil.getCellValueToString(row1.getCell(6))));
            taskWorkOrder.setWorkOrderNo(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(7))));
            taskWorkOrder.setWorkOrderLevel(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(8))));
            taskWorkOrder.setDeleteFlag(0);
            taskWorkOrder.setCreatedDate(new Date());
            orderMap.put(excelUtil.getCellValueToString(row1.getCell(1)), taskWorkOrder.getId());
            taskWorkOrderList.add(taskWorkOrder);
        }
        int lastRowNum4 = sheet6.getLastRowNum();
        for (int i = 1; i <= lastRowNum4; i++) {
            Row row1 = sheet6.getRow(i);
            ModuleTaskFile moduleTaskFile = new ModuleTaskFile();
            moduleTaskFile.setId(UUID.randomUUID().toString());
            moduleTaskFile.setTaskId(taskMap.get(excelUtil.getCellValueToString(row1.getCell(2))));
            moduleTaskFile.setFileName(excelUtil.getCellValueToString(row1.getCell(3)));
            try {
                moduleTaskFile.setFileType(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(4))));
            } catch (NumberFormatException e) {
            }
            moduleTaskFile.setFileUrl(excelUtil.getCellValueToString(row1.getCell(5)));
            moduleTaskFile.setDeleteFlag(0);
            moduleTaskFile.setCreatedDate(new Date());
            moduleTaskFile.setFileFirstImg(excelUtil.getCellValueToString(row1.getCell(6)));
            taskFileList.add(moduleTaskFile);
        }
        int lastRowNum5 = sheet7.getLastRowNum();
        for (int i = 1; i <= lastRowNum5; i++) {
            Row row1 = sheet7.getRow(i);
            WorkOrderFile workOrderFile = new WorkOrderFile();
            workOrderFile.setId(UUID.randomUUID().toString());
            workOrderFile.setWorkOrderId(orderMap.get(excelUtil.getCellValueToString(row1.getCell(2))));
            workOrderFile.setFileName(excelUtil.getCellValueToString(row1.getCell(3)));
            try {
                workOrderFile.setFileType(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(4))));
            } catch (NumberFormatException e) {
            }

            workOrderFile.setFileUrl(excelUtil.getCellValueToString(row1.getCell(5)));
            workOrderFile.setDeleteFlag(0);

            workOrderFileList.add(workOrderFile);
        }
        int lastRowNum6 = sheet8.getLastRowNum();
        for (int i = 1; i <= lastRowNum6; i++) {
            Row row1 = sheet8.getRow(i);
            KnowledgePoint knowledgePoint = new KnowledgePoint();
            knowledgePoint.setId(UUID.randomUUID().toString());
            knowledgePoint.setType(20);
            knowledgePoint.setModuleTaskId(taskMap.get(excelUtil.getCellValueToString(row1.getCell(2))));
            knowledgePoint.setKnowledgePoint(excelUtil.getCellValueToString(row1.getCell(3)));
            knowledgePoint.setLink(excelUtil.getCellValueToString(row1.getCell(4)));
            knowledgePointMap.put(excelUtil.getCellValueToString(row1.getCell(1)), knowledgePoint.getId());

            knowledgePointList.add(knowledgePoint);
        }
        int lastRowNum7 = sheet9.getLastRowNum();
        for (int i = 1; i <= lastRowNum7; i++) {
            Row row1 = sheet9.getRow(i);
            KnowledgePointFile knowledgePointFile = new KnowledgePointFile();
            knowledgePointFile.setId(UUID.randomUUID().toString());
            knowledgePointFile.setKnowledgePointId(knowledgePointMap.get(excelUtil.getCellValueToString(row1.getCell(2))));
            knowledgePointFile.setFileName(excelUtil.getCellValueToString(row1.getCell(3)));
            knowledgePointFile.setDeleteFlag(0);
            try {
                knowledgePointFile.setFileType(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(4))));
            } catch (NumberFormatException e) {
                knowledgePointFile.setFileType(20);
            }
            knowledgePointFile.setFileUrl(excelUtil.getCellValueToString(row1.getCell(5)));
            knowledgePointFile.setFileFirstImg(excelUtil.getCellValueToString(row1.getCell(6)));
            knowledgePointFileList.add(knowledgePointFile);
        }

        int lastRowNum8 = sheet10.getLastRowNum();
        for (int i = 1; i <= lastRowNum8; i++) {
            Row row1 = sheet10.getRow(i);
            ProjectVideo projectVideo = new ProjectVideo();
            projectVideo.setId(UUID.randomUUID().toString());
            projectVideo.setProjectId(project.getId());
            projectVideo.setVideoName(excelUtil.getCellValueToString(row1.getCell(3)));
            projectVideo.setVideoUrl(excelUtil.getCellValueToString(row1.getCell(4)));
            projectVideo.setFileFirstImg(excelUtil.getCellValueToString(row1.getCell(5)));
            projectVideo.setDeleteFlag(0);
            projectVideo.setCreatedDate(new Date());
            videoRepository.save(projectVideo);
        }

        projectRepository.save(project);
        fileRepository.save(projectFileList);
        moduleRepository.save(projectModuleList);
        moduleTaskRepository.save(moduleTaskList);
        workOrderRepository.save(taskWorkOrderList);
        taskFileRepository.save(taskFileList);
        workOrderFileRepository.save(workOrderFileList);
        knowledgePointRepository.save(knowledgePointList);
        knowledgePointFileRepository.save(knowledgePointFileList);
    }
    private Integer returnOrderNum(Map<String,Integer> map,String id){
        Integer integer = map.get(id);
        if (Objects.isNull(integer)){
            map.put(id,1);
            return 1;
        }else {
            integer++;
            map.put(id,integer);
            return integer;
        }
    }
    public Map<String,Object> importEasyProject(MultipartFile file, Long createBy) throws Exception {
        Map<String,Object> map=new HashMap<>();
        ExcelUtil excelUtil = new ExcelUtil(file.getInputStream(), "2003");
        if (!ExcelUtil.isExcel2003(file.getName())) {
            excelUtil = new ExcelUtil(file.getInputStream(), "2003");
        } else {
            excelUtil = new ExcelUtil(file.getInputStream(), "2007");

        }
        Sheet sheet1 = excelUtil.getWorkbook().getSheetAt(0);
        Sheet sheet3 = excelUtil.getWorkbook().getSheetAt(1);
        Sheet sheet4 = excelUtil.getWorkbook().getSheetAt(2);
        Sheet sheet5 = excelUtil.getWorkbook().getSheetAt(3);
        Map<String, String> moduleMap = new HashMap<>();
        Map<String, String> taskMap = new HashMap<>();
        Map<String, Integer> moduleOrderNumMap = new HashMap<>();
        Map<String, Integer> taskOrderNumMap = new HashMap<>();
        Map<String, String> orderMap = new HashMap<>();
        Project project = new Project();
        Row row = sheet1.getRow(1);
        project.setId(UUID.randomUUID().toString());
        project.setProjectName(excelUtil.getCellValueToString(row.getCell(1)));
        project.setProjectDesc(excelUtil.getCellValueToString(row.getCell(2)));
        project.setLanguage(excelUtil.getCellValueToString(row.getCell(3)));
        project.setClassHour(Float.valueOf(excelUtil.getCellValueToString(row.getCell(4))));
        project.setProjectType(excelUtil.getCellValueToString(row.getCell(5)));
        project.setIsPublish(0);
        project.setCreatedBy(createBy);
        project.setDeleteFlag(0);
        project.setCreatedDate(new Date());
        List<ProjectModule> projectModuleList = new ArrayList<>();
        List<ModuleTask> moduleTaskList = new ArrayList<>();
        List<TaskWorkOrder> taskWorkOrderList = new ArrayList<>();

        int lastRowNum1 = sheet3.getLastRowNum();
        for (int i = 1; i <= lastRowNum1; i++) {
            try {


            Row row1 = sheet3.getRow(i);
            ProjectModule projectModule = new ProjectModule();
            String value = excelUtil.getCellValueToString(row1.getCell(1));
            if (StringUtils.isEmpty(value)){
                continue;
            }
            projectModule.setId(UUID.randomUUID().toString());
            projectModule.setProjectId(project.getId());
            projectModule.setModuleName(value);
            if (StringUtils.isEmpty(projectModule.getModuleName())){
                continue;
            }
            moduleMap.put(projectModule.getModuleName(),projectModule.getId());
            try {
                projectModule.setModuleDesc(excelUtil.getCellValueToString(row1.getCell(2)));
            }catch (NumberFormatException e){
            }
            try {
                projectModule.setModuleLevel(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(3))));
            }catch (NumberFormatException e){
            }
            projectModule.setSuggestTime(Float.valueOf(excelUtil.getCellValueToString(row1.getCell(4))));
            projectModule.setModuleNo(i);
            projectModule.setDeleteFlag(0);
            projectModule.setCreatedDate(new Date());
            projectModuleList.add(projectModule);
            }catch (Exception e){
                throw new Exception("模块列表行"+i+"行错误");
            }
        }
        int lastRowNum2 = sheet4.getLastRowNum();
        for (int i = 1; i <= lastRowNum2; i++) {
            try {
            Row row1 = sheet4.getRow(i);
            ModuleTask moduleTask = new ModuleTask();
            moduleTask.setId(UUID.randomUUID().toString());
            String value=null;
            try{
                value = excelUtil.getCellValueToString(row1.getCell(1));
            }catch (NullPointerException e){
                continue;
            }
            if (StringUtils.isEmpty(value)){
                continue;
            }
            moduleTask.setModuleId(moduleMap.get(value));
            if (StringUtils.isEmpty(moduleTask.getModuleId())){
                map.put(ApiReturnConstants.SUCCESS,false);
                map.put(ApiReturnConstants.MESSAGE,"任务列表第"+i+"行模块名称不存在");
                return map;
            }
            moduleTask.setTaskName(excelUtil.getCellValueToString(row1.getCell(2)));
            taskMap.put(moduleTask.getTaskName(),moduleTask.getId());
            moduleTask.setTaskDesc(excelUtil.getCellValueToString(row1.getCell(3)));
            moduleTask.setSuggestTime(Float.valueOf(excelUtil.getCellValueToString(row1.getCell(4))));
            moduleTask.setTaskLevel(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(5))));
            moduleTask.setTaskStep(excelUtil.getCellValueToString(row1.getCell(6)));
            moduleTask.setTaskNo(returnOrderNum(moduleOrderNumMap,moduleTask.getModuleId()));
            moduleTask.setDeleteFlag(0);
            moduleTask.setCreatedDate(new Date());
            moduleTaskList.add(moduleTask);
            }catch (Exception e){
                throw new  Exception("任务列表行"+i+"行错误");
            }
        }
        int lastRowNum3 = sheet5.getLastRowNum();
        for (int i = 1; i <= lastRowNum3; i++) {
            try {


            Row row1 = sheet5.getRow(i);
            TaskWorkOrder taskWorkOrder = new TaskWorkOrder();
            taskWorkOrder.setId(UUID.randomUUID().toString());
            String value =null;
            try{
                value = excelUtil.getCellValueToString(row1.getCell(1));
            }catch (NullPointerException e){
                continue;
            }
            if (StringUtils.isEmpty(value)){
                continue;
            }
            taskWorkOrder.setTaskId(taskMap.get(value));
                if (StringUtils.isEmpty(taskWorkOrder.getTaskId())){
                    map.put(ApiReturnConstants.SUCCESS,false);
                    map.put(ApiReturnConstants.MESSAGE,"工单列表第"+i+"行任务名称不存在");
                    return map;
                }
            taskWorkOrder.setWorkOrderName(excelUtil.getCellValueToString(row1.getCell(2)));
            taskWorkOrder.setWorkOrderDesc(excelUtil.getCellValueToString(row1.getCell(3)));
            taskWorkOrder.setSuggestTime(Float.valueOf(excelUtil.getCellValueToString(row1.getCell(4))));
            taskWorkOrder.setWorkOrderLevel(Integer.parseInt(excelUtil.getCellValueToString(row1.getCell(5))));
            taskWorkOrder.setWorkOrderNo(returnOrderNum(taskOrderNumMap,taskWorkOrder.getTaskId()));
            taskWorkOrder.setDeleteFlag(0);
            taskWorkOrder.setCreatedDate(new Date());
            taskWorkOrderList.add(taskWorkOrder);
            }catch (Exception e){
                throw new  Exception("工单列表行"+i+"行错误");
            }
        }
        projectModuleList.forEach(item->{
            item.setTaskNum(returnOrderNum(moduleOrderNumMap,item.getId())-1);
        });
        moduleTaskList.forEach(item->{
            item.setWorkOrderNum(returnOrderNum(taskOrderNumMap,item.getId())-1);
        });
        project.setTaskNum(moduleTaskList.size());
        project.setModuleNum(projectModuleList.size());
        project.setWorkOrderNum(taskWorkOrderList.size());
        projectRepository.save(project);
        moduleRepository.save(projectModuleList);
        moduleTaskRepository.save(moduleTaskList);
        workOrderRepository.save(taskWorkOrderList);
        map.put(ApiReturnConstants.SUCCESS,true);
        map.put(ApiReturnConstants.MESSAGE,"导入成功");
        return map;
    }


}
